EasyExcel导入Excel文件，并对文件内容作校验

2023-04-03 06:14| 来源: 网络整理| 查看: 265

首页是pom文件导入EasyExcel的依赖

com.alibaba easyexcel 3.2.0

mysql中添加三个字段做测试

自定义异常类

package com.example.demo.config; import lombok.Data; import lombok.EqualsAndHashCode; @EqualsAndHashCode(callSuper = true) @Data public class ImportException extends RuntimeException { private String msg; private String code; public ImportException() { super(); } public ImportException(String code, String message) { super(message); this.code = code; this.msg = message; } public ImportException(String message) { super(message); this.msg = message; } public String getCode() { return code; } public String getMessage() { return msg; } } package com.example.demo.config; import com.example.demo.response.BaseResponse; import com.example.demo.response.RespGenerator; import org.apache.ibatis.logging.Log; import org.apache.ibatis.logging.LogFactory; import org.springframework.web.bind.annotation.ExceptionHandler; import org.springframework.web.bind.annotation.RestControllerAdvice; @RestControllerAdvice public class GlobalExceptionHandler { private static final Log logger = LogFactory.getLog(GlobalExceptionHandler.class); @ExceptionHandler(ImportException.class) public BaseResponse businessException(ImportException importException) { return RespGenerator.fail(importException.getCode(),importException.getMsg()); } } BaseResponse package com.example.demo.response; import lombok.Data; @Data public class BaseResponse { private String code; private String message; private T data; /** * * 默认构造方法 * * @param code * 状态码 * @param message * 接口信息 * @param data * 接口数据 */ public BaseResponse(String code, String message, T data) { super(); this.code = code; this.message = message; this.data = data; } /** * 默认构造方法 */ public BaseResponse() { super(); } }

实体类User

其中value中的值跟Excel中的表头名称对应，index与excel中的表头位置对应

package com.example.demo.model.entity; import com.alibaba.excel.annotation.ExcelProperty; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.extension.activerecord.Model; import lombok.*; import lombok.experimental.Accessors; import java.io.Serializable; @Data @EqualsAndHashCode(callSuper = true) //@Accessors(chain = true) @Builder @AllArgsConstructor @NoArgsConstructor public class User extends Model implements Serializable { private static final long serialVersionUID = 1L; // value 通过标题文本对应 index 通过文本行号对应 @ExcelProperty(value = "id", index = 0) @TableField("uid") private String uid; @ExcelProperty(value = "名称", index = 1) @TableField("userName") private String userName; @ExcelProperty(value = "密码", index = 2) @TableField("password") private String password; }

controller中

@Slf4j的依赖可自行百度导入

package com.example.demo.controller; import com.example.demo.response.BaseResponse; import com.example.demo.service.ImportService; import lombok.extern.slf4j.Slf4j; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @RestController @Slf4j @RequestMapping(value = "/import") public class ImportController { @Resource private ImportService importService; /** * 导入Excel * @param file * @return */ @PostMapping("/upLoad") public BaseResponse upLoad(@RequestParam("file") MultipartFile file) { return importService.importExcel(file); } }

service

import com.example.demo.response.BaseResponse; import org.springframework.web.multipart.MultipartFile; public interface ImportService { BaseResponse importExcel(MultipartFile file); } ImportServiceImpl package com.example.demo.service.impl; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.example.demo.config.ImportException; import com.example.demo.config.UserReadListener; import com.example.demo.model.entity.User; import com.example.demo.response.BaseResponse; import com.example.demo.service.IUserService; import com.example.demo.service.ImportService; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.ObjectUtils; import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.List; @Service @Slf4j public class ImportServiceImpl implements ImportService { /** * 每隔2条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 2; @Resource private IUserService userService; @Transactional @Override public BaseResponse importExcel(MultipartFile file){ // 判断文件是否为空 if (ObjectUtils.isEmpty(file) || file.getSize() = BATCH_COUNT) { saveData(); // 持久化逻辑 // 存储完成清理 list dataList.clear(); }*/ } private static void validChoiceInfo(T object, AnalysisContext context) { // 泛型为用户 if(object instanceof User) { //泛型转换为实体类型 Object temp = object; User user = (User)temp; if(StringUtils.isBlank(user.getUid())){ log.info("上传失败:第{}行ID信息为空",context.readRowHolder().getRowIndex()); throw new ImportException("上传失败:第"+context.readRowHolder().getRowIndex().toString()+"行ID信息为空"); } if(StringUtils.isBlank(user.getUserName())){ log.info(String.format("上传失败:第{}行用户名信息为空",context.readRowHolder().getRowIndex())); throw new ImportException(String.format("上传失败:第{}行用户名信息为空",context.readRowHolder().getRowIndex())); } if(StringUtils.isBlank(user.getPassword())){ log.info(String.format("上传失败:第{}行密码信息为空",context.readRowHolder().getRowIndex())); throw new ImportException(String.format("上传失败:第{}行密码信息为空",context.readRowHolder().getRowIndex())); } } } /** * 所有数据解析完成会来调用 */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 log.info("doAfterAllAnalysed---开始处理"); if(dataList.isEmpty() || dataList.size() == 0) { throw new ImportException("上传失败，Excel中无数据"); } log.info("所有数据解析完成！"); } public List getData() { return dataList; } } IUserService package com.example.demo.service; import com.baomidou.mybatisplus.core.metadata.IPage; import com.example.demo.model.entity.User; import com.baomidou.mybatisplus.extension.service.IService; import com.example.demo.model.bo.UserDetailBO; import com.example.demo.model.bo.UserLoginBO; import com.example.demo.model.vo.GetUserVO; import com.example.demo.response.BaseResponse; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletRequest; import java.io.IOException; /** *

* 服务类 *

* * @since 2023-03-16 */ public interface IUserService extends IService { } UserServiceImpl package com.example.demo.service.impl; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.example.demo.config.BaseErrorEnum; import com.example.demo.config.BaseException; import com.example.demo.model.entity.User; import com.example.demo.mapper.UserMapper; import com.example.demo.model.bo.UserDetailBO; import com.example.demo.model.bo.UserLoginBO; import com.example.demo.model.vo.GetUserVO; import com.example.demo.service.IUserService; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.example.demo.utils.RedisUtils; import com.example.demo.utils.TokenUtils; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import java.util.ArrayList; import java.util.List; import java.util.Objects; import java.util.concurrent.TimeUnit; /** *

* 服务实现类 *

* @since 2023-03-16 */ @Service @Slf4j public class UserServiceImpl extends ServiceImpl implements IUserService { } UserMapper package com.example.demo.mapper; import com.example.demo.model.entity.User; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.apache.ibatis.annotations.Mapper; /** *

* Mapper 接口 *

* * @since 2023-03-16 */ @Mapper public interface UserMapper extends BaseMapper { }

postman测试，选择文件

导入成功

【本文地址】

公司简介

联系我们